package com.atguigu.dga.governance.mapper;

import com.atguigu.dga.governance.bean.GovernanceAssessTable;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import com.baomidou.dynamic.datasource.annotation.DS;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 表治理考评情况 Mapper 接口
 * </p>
 *
 * @author zhangchen
 * @since 2024-08-02
 */
@Mapper
@DS("dga")
public interface GovernanceAssessTableMapper extends BaseMapper<GovernanceAssessTable> {

    @Select("     select #{assessDate} as assess_date , ad.table_name,ad.schema_name ,ad.tec_owner,\n" +
            "     ifnull(avg(if(governance_type='SPEC',  assess_score,null)),0) score_spec_avg,\n" +
            "     ifnull(avg(if(governance_type='STORAGE',  assess_score,null)),0)  score_storage_avg,\n" +
            "     ifnull(avg(if(governance_type='CALC',  assess_score,null)),0)  score_calc_avg,\n" +
            "     ifnull(avg(if(governance_type='QUALITY',  assess_score,null)),0)  score_quality_avg,\n" +
            "     ifnull(avg(if(governance_type='SECURITY',  assess_score,null)),0)  score_security_avg,\n" +
            "     (ifnull(avg(if(governance_type='SPEC',  assess_score*gt.type_weight,null)),0)\n" +
            "     +ifnull(avg(if(governance_type='STORAGE',  assess_score*gt.type_weight,null)),0)\n" +
            "     +ifnull(avg(if(governance_type='CALC',  assess_score*gt.type_weight,null)),0)\n" +
            "     +ifnull(avg(if(governance_type='QUALITY',  assess_score*gt.type_weight,null)),0)\n" +
            "     +ifnull(avg(if(governance_type='SECURITY',  assess_score*gt.type_weight,null)) ,0))/100  score_on_type_weight,\n" +
            "     sum(if(assess_score<10,1,0)) problem_num ,\n" +
            "     now() create_time\n" +
            "     from  governance_assess_detail ad  join governance_type gt on gt.type_code=governance_type\n" +
            "     where  assess_date=#{assessDate}\n" +
            "     group by  ad.schema_name ,ad.table_name,ad.tec_owner")
    List<GovernanceAssessTable> selectAssessTableList(@Param("assessDate") String assessDate);

}
